Workaround "permission denied for schema auth" on Supabase

The auth schema is protected and only accessible via the postgres role, so we can’t directly grant the permissions:

postgres=> GRANT USAGE ON SCHEMA auth TO your_role;
WARNING:  no privileges were granted for "auth"
GRANT

And even if the schema grant succeeded, some tables are also protected from GRANT SELECT:

postgres=> GRANT SELECT ON ALL TABLES IN SCHEMA auth TO your_role;
WARNING:  no privileges were granted for "oauth_consents"
WARNING:  no privileges were granted for "oauth_clients"
WARNING:  no privileges were granted for "oauth_authorizations"
WARNING:  no privileges were granted for "oauth_client_states"
GRANT

Solution

Instead, you could use PostgreSQL’s role inheritance to workaround the limitations at the cost of granting a broader permission:

GRANT postgres TO your_role;